﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.SqlClient;

namespace TB.DALSqlProvider
{
    /// <summary>
    /// 对*MpRespMsg*操作数据库(针对SQL Server)的实现
    /// </summary>
    public class MpRespMsgSqlDALProvider:IDAL.IDALMpRespMsg
    { 
        public int Insert(TB.Entity.MpRespMsg obj)
        {
           //1.通过database工厂得到当前数据库连接的对象  db
            Database db = DatabaseFactory.CreateDatabase();
            //2.通过db 获得一个sp构成的cmd
            var cmd = db.GetStoredProcCommand("proc_MpRespMsg_Insert") as SqlCommand;
            //3.cmd参数赋值
            //cmd.Parameters.AddWithValue("@id_auto",(obj.id_auto==0||obj.id_auto==null)?DBNull.Value:(object)obj.id_auto);
            cmd.Parameters.AddWithValue("@keyword",(obj.keyword==String.Empty||obj.keyword==null)?DBNull.Value:(object)obj.keyword);
            cmd.Parameters.AddWithValue("@msgType",(obj.msgType==String.Empty||obj.msgType==null)?DBNull.Value:(object)obj.msgType);
            cmd.Parameters.AddWithValue("@text_Content",(obj.text_Content==String.Empty||obj.text_Content==null)?DBNull.Value:(object)obj.text_Content);
            cmd.Parameters.AddWithValue("@music_MusicUrl",(obj.music_MusicUrl==String.Empty||obj.music_MusicUrl==null)?DBNull.Value:(object)obj.music_MusicUrl);
            cmd.Parameters.AddWithValue("@music_HQMusicUrl",(obj.music_HQMusicUrl==String.Empty||obj.music_HQMusicUrl==null)?DBNull.Value:(object)obj.music_HQMusicUrl);
            cmd.Parameters.AddWithValue("@news_Title",(obj.news_Title==String.Empty||obj.news_Title==null)?DBNull.Value:(object)obj.news_Title);
            cmd.Parameters.AddWithValue("@news_Description",(obj.news_Description==String.Empty||obj.news_Description==null)?DBNull.Value:(object)obj.news_Description);
            cmd.Parameters.AddWithValue("@news_PicUrl",(obj.news_PicUrl==String.Empty||obj.news_PicUrl==null)?DBNull.Value:(object)obj.news_PicUrl);
            cmd.Parameters.AddWithValue("@news_Url",(obj.news_Url==String.Empty||obj.news_Url==null)?DBNull.Value:(object)obj.news_Url);
            cmd.Parameters.AddWithValue("@r1",(obj.r1==String.Empty||obj.r1==null)?DBNull.Value:(object)obj.r1);
           // cmd.Parameters.AddWithValue("@r2",(obj.r2==String.Empty||obj.r2==null)?DBNull.Value:(object)obj.r2);
            cmd.Parameters.AddWithValue("@r3",(obj.r3==String.Empty||obj.r3==null)?DBNull.Value:(object)obj.r3);
            //4.重要:通过db来执行这个cmd (在db内部对cmd进行构造)
           return db.ExecuteNonQuery(cmd);
        }
        
        public int Update(TB.Entity.MpRespMsg obj)
        {
            //1.通过database工厂得到当前数据库连接的对象  db
            Database db = DatabaseFactory.CreateDatabase();
            //2.通过db 获得一个sp构成的cmd
            var cmd = db.GetStoredProcCommand("proc_MpRespMsg_Update") as SqlCommand;
            //3.cmd参数赋值
            cmd.Parameters.AddWithValue("@id_auto",(obj.id_auto==0)?DBNull.Value:(object)obj.id_auto);
            cmd.Parameters.AddWithValue("@keyword",(obj.keyword==String.Empty||obj.keyword==null)?DBNull.Value:(object)obj.keyword);
            cmd.Parameters.AddWithValue("@msgType",(obj.msgType==String.Empty||obj.msgType==null)?DBNull.Value:(object)obj.msgType);
            cmd.Parameters.AddWithValue("@text_Content",(obj.text_Content==String.Empty||obj.text_Content==null)?DBNull.Value:(object)obj.text_Content);
            cmd.Parameters.AddWithValue("@music_MusicUrl",(obj.music_MusicUrl==String.Empty||obj.music_MusicUrl==null)?DBNull.Value:(object)obj.music_MusicUrl);
            cmd.Parameters.AddWithValue("@music_HQMusicUrl",(obj.music_HQMusicUrl==String.Empty||obj.music_HQMusicUrl==null)?DBNull.Value:(object)obj.music_HQMusicUrl);
            cmd.Parameters.AddWithValue("@news_Title",(obj.news_Title==String.Empty||obj.news_Title==null)?DBNull.Value:(object)obj.news_Title);
            cmd.Parameters.AddWithValue("@news_Description",(obj.news_Description==String.Empty||obj.news_Description==null)?DBNull.Value:(object)obj.news_Description);
            cmd.Parameters.AddWithValue("@news_PicUrl",(obj.news_PicUrl==String.Empty||obj.news_PicUrl==null)?DBNull.Value:(object)obj.news_PicUrl);
            cmd.Parameters.AddWithValue("@news_Url",(obj.news_Url==String.Empty||obj.news_Url==null)?DBNull.Value:(object)obj.news_Url);
            cmd.Parameters.AddWithValue("@r1",(obj.r1==String.Empty||obj.r1==null)?DBNull.Value:(object)obj.r1);
            cmd.Parameters.AddWithValue("@r2",(obj.r2==String.Empty||obj.r2==null)?DBNull.Value:(object)obj.r2);
            cmd.Parameters.AddWithValue("@r3",(obj.r3==String.Empty||obj.r3==null)?DBNull.Value:(object)obj.r3);
            //4.重要:通过db来执行这个cmd (在db内部对cmd进行构造)
            return db.ExecuteNonQuery(cmd);
        }
        
        public int Delete(TB.Entity.MpRespMsg obj)
        {
            //1.通过database工厂得到当前数据库连接的对象  db
            Database db = DatabaseFactory.CreateDatabase();
            //2.通过db 获得一个sp构成的cmd
            var cmd = db.GetStoredProcCommand("proc_MpRespMsg_Delete") as SqlCommand;
            //3.cmd参数赋值
           cmd.Parameters.AddWithValue("@id_auto", obj.id_auto);
            //4.重要:通过db来执行这个cmd (在db内部对cmd进行构造)
            return db.ExecuteNonQuery(cmd);
        }
        
        public List<TB.Entity.MpRespMsg> LoadAll()
        {
            //1.通过database工厂得到当前数据库连接的对象  db
            Database db = DatabaseFactory.CreateDatabase();
            //2.通过db 获得一个sp构成的cmd
            var cmd = db.GetStoredProcCommand("proc_MpRespMsg_LoadAll") as SqlCommand;
            //3.通过db执行一个DBReader
            var reader =  db.ExecuteReader(cmd);
            //4.遍历DBReader,生成新的Entity对象,然后添加到List
            List<TB.Entity.MpRespMsg> list = new List<TB.Entity.MpRespMsg>();
            while (reader.Read())
            {
               list.Add(new TB.Entity.MpRespMsg()
               {
                    id_auto=reader["id_auto"]==DBNull.Value?0:(int)reader["id_auto"],
                    keyword=reader["keyword"]==DBNull.Value?String.Empty:(string)reader["keyword"],
                    msgType=reader["msgType"]==DBNull.Value?String.Empty:(string)reader["msgType"],
                    text_Content=reader["text_Content"]==DBNull.Value?String.Empty:(string)reader["text_Content"],
                    music_MusicUrl=reader["music_MusicUrl"]==DBNull.Value?String.Empty:(string)reader["music_MusicUrl"],
                    music_HQMusicUrl=reader["music_HQMusicUrl"]==DBNull.Value?String.Empty:(string)reader["music_HQMusicUrl"],
                    news_Title=reader["news_Title"]==DBNull.Value?String.Empty:(string)reader["news_Title"],
                    news_Description=reader["news_Description"]==DBNull.Value?String.Empty:(string)reader["news_Description"],
                    news_PicUrl=reader["news_PicUrl"]==DBNull.Value?String.Empty:(string)reader["news_PicUrl"],
                    news_Url=reader["news_Url"]==DBNull.Value?String.Empty:(string)reader["news_Url"],
                    r1=reader["r1"]==DBNull.Value?String.Empty:(string)reader["r1"],
                    r2=reader["r2"]==DBNull.Value?String.Empty:(string)reader["r2"],
                    r3=reader["r3"]==DBNull.Value?String.Empty:(string)reader["r3"],
               });
           }
           return list;
        }
        
        public TB.Entity.MpRespMsg LoadById(int id_auto)
        {
            //1.通过database工厂得到当前数据库连接的对象  db
            Database db = DatabaseFactory.CreateDatabase();
            //2.通过db 获得一个sp构成的cmd
            var cmd = db.GetStoredProcCommand("proc_MpRespMsg_LoadById") as SqlCommand;
            //3.通过db执行一个DBReader
            cmd.Parameters.AddWithValue("@id_auto", id_auto);
            var reader = db.ExecuteReader(cmd);
            //4.遍历DBReader,生成一个新的Entity对象 
            TB.Entity.MpRespMsg entity = null;
            while (reader.Read())
            {
                entity = new TB.Entity.MpRespMsg()
                {
                    id_auto=reader["id_auto"]==DBNull.Value?0:(int)reader["id_auto"],
                    keyword=reader["keyword"]==DBNull.Value?String.Empty:(string)reader["keyword"],
                    msgType=reader["msgType"]==DBNull.Value?String.Empty:(string)reader["msgType"],
                    text_Content=reader["text_Content"]==DBNull.Value?String.Empty:(string)reader["text_Content"],
                    music_MusicUrl=reader["music_MusicUrl"]==DBNull.Value?String.Empty:(string)reader["music_MusicUrl"],
                    music_HQMusicUrl=reader["music_HQMusicUrl"]==DBNull.Value?String.Empty:(string)reader["music_HQMusicUrl"],
                    news_Title=reader["news_Title"]==DBNull.Value?String.Empty:(string)reader["news_Title"],
                    news_Description=reader["news_Description"]==DBNull.Value?String.Empty:(string)reader["news_Description"],
                    news_PicUrl=reader["news_PicUrl"]==DBNull.Value?String.Empty:(string)reader["news_PicUrl"],
                    news_Url=reader["news_Url"]==DBNull.Value?String.Empty:(string)reader["news_Url"],
                    r1=reader["r1"]==DBNull.Value?String.Empty:(string)reader["r1"],
                    r2=reader["r2"]==DBNull.Value?String.Empty:(string)reader["r2"],
                    r3=reader["r3"]==DBNull.Value?String.Empty:(string)reader["r3"],
                };
            }
            return entity;
        }

        public List<TB.Entity.MpRespMsg> LoadAll(string keyword)
        {
            //1.通过database工厂得到当前数据库连接的对象  db
            Database db = DatabaseFactory.CreateDatabase();
            //2.通过db 获得一个sp构成的cmd
            string strSql = "select *from MpRespMsg where keyword='" + keyword + "'";
            var cmd = db.GetSqlStringCommand(strSql) as SqlCommand;
            //var cmd = db.GetStoredProcCommand("proc_MpRespMsg_LoadAll") as SqlCommand;
            //3.通过db执行一个DBReader
            var reader = db.ExecuteReader(cmd);
            //4.遍历DBReader,生成新的Entity对象,然后添加到List
            List<TB.Entity.MpRespMsg> list = new List<TB.Entity.MpRespMsg>();
            while (reader.Read())
            {
                list.Add(new TB.Entity.MpRespMsg()
                {
                    id_auto = reader["id_auto"] == DBNull.Value ? 0 : (int)reader["id_auto"],
                    keyword = reader["keyword"] == DBNull.Value ? String.Empty : (string)reader["keyword"],
                    msgType = reader["msgType"] == DBNull.Value ? String.Empty : (string)reader["msgType"],
                    text_Content = reader["text_Content"] == DBNull.Value ? String.Empty : (string)reader["text_Content"],
                    music_MusicUrl = reader["music_MusicUrl"] == DBNull.Value ? String.Empty : (string)reader["music_MusicUrl"],
                    music_HQMusicUrl = reader["music_HQMusicUrl"] == DBNull.Value ? String.Empty : (string)reader["music_HQMusicUrl"],
                    news_Title = reader["news_Title"] == DBNull.Value ? String.Empty : (string)reader["news_Title"],
                    news_Description = reader["news_Description"] == DBNull.Value ? String.Empty : (string)reader["news_Description"],
                    news_PicUrl = reader["news_PicUrl"] == DBNull.Value ? String.Empty : (string)reader["news_PicUrl"],
                    news_Url = reader["news_Url"] == DBNull.Value ? String.Empty : (string)reader["news_Url"],
                    r1 = reader["r1"] == DBNull.Value ? String.Empty : (string)reader["r1"],
                    r2 = reader["r2"] == DBNull.Value ? String.Empty : reader["r2"].ToString(),
                    r3 = reader["r3"] == DBNull.Value ? String.Empty : (string)reader["r3"],
                });
            }
            return list;
        }

        public System.Data.DataSet GetDataSet(string strSql)
        {
            Database db = DatabaseFactory.CreateDatabase();
            var cmd = db.GetSqlStringCommand(strSql) as SqlCommand;
            System.Data.DataSet ds = db.ExecuteDataSet(cmd);
            return ds;
        }


        public int Update(string colName, string value,int auto_id)
        {
            string strSql = "update MpRespMsg set " + colName + "='" + value + "' where id_auto=" + auto_id;
            Database db = DatabaseFactory.CreateDatabase();
            var cmd = db.GetSqlStringCommand(strSql) as SqlCommand;
            return db.ExecuteNonQuery(cmd);
        }
    }
}